/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.OEspecificoVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class OEspecificoDao extends DBase {

    Connection conn = null;

    public OEspecificoDao() {
        super();
    }

    private void setOEspecifico(OEspecificoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODOBJ_PK(rs.getInt("NUM_CODOBJ_PK"));
        bean.setNUM_CODOBJESP_PK(rs.getInt("NUM_CODOBJESP_PK"));
        bean.setNUM_IDOBJ(rs.getInt("NUM_IDOBJ"));
        bean.setNUM_IDOBJESP(rs.getInt("NUM_IDOBJESP"));
        bean.setTX_DESCOBJESP(rs.getString("TX_DESCOBJESP"));
    }

    private void setOEspecificoCombo(OEspecificoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODOBJ_PK(rs.getInt("NUM_CODOBJ_PK"));
        bean.setNUM_CODOBJESP_PK(rs.getInt("NUM_CODOBJESP_PK"));
        bean.setTX_DESCOBJESP(rs.getString("TX_DESCOBJESP"));
    }

    public String spMan(int tipo, OEspecificoVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_OBJESP(?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODOBJESP_PK());
            cs.setInt(3, bean.getNUM_CODOBJ_PK());
            cs.setInt(4, bean.getNUM_IDOBJESP());
            cs.setString(5, bean.getTX_DESCOBJESP());
            cs.execute();
        } catch (Exception e) {
            msj = e.toString();
            JOptionPane.showMessageDialog(null, e.toString());
            return msj;
        }
        return msj;
    }

    public ArrayList<OEspecificoVO> selectLista() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<OEspecificoVO> lista = new ArrayList<OEspecificoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODOBJESP_PK,TX_DESCOBJESP from ME_OBJESP";
            rs = stmt.executeQuery(sql);
            OEspecificoVO bean = null;
            while (rs.next()) {
                bean = new OEspecificoVO();
                setOEspecifico(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public List getlist() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<OEspecificoVO> lista = new ArrayList<OEspecificoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODOBJ_PK,TX_DESCOBJESP,NUM_CODOBJESP_PK from ME_OBJESP";
            rs = stmt.executeQuery(sql);
            OEspecificoVO bean = null;
            while (rs.next()) {
                bean = new OEspecificoVO();
                setOEspecifico(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public List getListById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<OEspecificoVO> lista = new ArrayList<OEspecificoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "select og.NUM_IDOBJ,oe.NUM_IDOBJESP,oe.NUM_CODOBJ_PK,oe.TX_DESCOBJESP,oe.NUM_CODOBJESP_PK from ME_OBJESP oe inner join ME_OBJ og on og.NUM_CODOBJ_PK=oe.NUM_CODOBJ_PK where oe.NUM_CODOBJ_PK=? order by NUM_IDOBJESP";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            OEspecificoVO bean = null;
            while (rs.next()) {
                bean = new OEspecificoVO();
                setOEspecifico(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public OEspecificoVO selectById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        OEspecificoVO bean = null;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            String sql = "select NUM_CODOBJESP_PK,TX_DESCOBJESP from ME_OBJESP where NUM_CODOBJESP_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new OEspecificoVO();
            if (rs.next()) {
                setOEspecifico(bean, rs);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }

        return bean;
    }



     public List getCombo(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<OEspecificoVO> lista = new ArrayList<OEspecificoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "select NUM_CODOBJESP_PK,TX_DESCOBJESP,NUM_CODOBJ_PK from ME_OBJESP where NUM_CODOBJ_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            OEspecificoVO bean = null;
            while (rs.next()) {
                bean = new OEspecificoVO();
                setOEspecificoCombo(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }
}
